﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Vote.DataEntity;

namespace Vote.Database.DataAccess
{
    public class UserActionAccess
    {
        /// <summary>
        /// 获取投票配置信息
        /// </summary>
        /// <param name="id"></param>
        public ActionDataEntity GetActionInfo(int actionId)
        {
            MySqlParameter[] parms = new MySqlParameter[] { new MySqlParameter("id", MySqlDbType.Int32) };
            parms[0].Value = actionId;
            string sql = "SELECT a.Id, a.ActionName, a.ActionDesc, a.Begintime, a.Endtime FROM action a WHERE a.Id=?id";

            ActionDataEntity actionConfig = null;
            using (var reader = MySqlHelper.ExecuteReader(MySqlHelper.ConnectionStringManager, CommandType.Text, sql, parms))
            {
                if (reader.Read())
                {
                    actionConfig = new ActionDataEntity()
                   {
                       ID = Convert.ToInt32(reader["ID"]),
                       ActionName = reader["ActionName"] != DBNull.Value ? reader["ActionName"].ToString() : string.Empty,
                       ActionDesc = reader["ActionDesc"] != DBNull.Value ? reader["ActionDesc"].ToString() : string.Empty,
                       BeginTime = reader["Begintime"] != DBNull.Value ? DateTime.Parse(reader["Begintime"].ToString()) : DateTime.MinValue,
                       EndTime = reader["Endtime"] != DBNull.Value ? DateTime.Parse(reader["Endtime"].ToString()) : DateTime.MinValue
                   };

                }
            }

            return actionConfig;
        }

        /// <summary>
        /// 获取用户投票
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="actionId"></param>
        /// <returns></returns>
        public Int32 GetUserAction(String userId, Int32 actionId)
        {
            MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter("userId", MySqlDbType.String), 
                                        new MySqlParameter("actionId", MySqlDbType.String) };
            parms[0].Value = userId;
            parms[1].Value = actionId;
            string sql = "SELECT COUNT(1) as num FROM vote a WHERE a.UserId=?userId AND a.ActionId=?actionId";
            Int32 result = 0;
            Object obj = MySqlHelper.ExecuteScalar(MySqlHelper.ConnectionStringManager, CommandType.Text, sql, parms);
            if (null != obj)
            {
                result = Convert.ToInt32(obj);
            }
            return result;
        }



        /// <summary>
        /// 保存投票结果
        /// </summary>
        /// <param name="actionId"></param>
        /// <param name="userId"></param>
        /// <param name="selectedVoteItem"></param>
        /// <returns></returns>
        public Boolean SaveVotingResult(int actionId, string userId, List<int> selectedVoteItem)
        {
            Boolean result = true;
            String sqlVote = "INSERT INTO vote(Actionid,UserId) VALUES(" + actionId + ",'" + userId + "');SELECT last_insert_id();";
            StringBuilder sBuilder = new StringBuilder("INSERT INTO result(Itemid,`Result`,Voteid) VALUES");

            MySqlConnection conn = new MySqlConnection(MySqlHelper.ConnectionStringManager);
            conn.Open();
            MySqlTransaction tran = conn.BeginTransaction();
            MySqlCommand command = new MySqlCommand();
            command.Connection = conn;
            command.Transaction = tran;
            try
            {
                command.CommandText = sqlVote;
                object id = command.ExecuteScalar();
                if (null != id)
                {

                    int voteid = Convert.ToInt32(id);
                    for (int i = 0; i < selectedVoteItem.Count; i++)
                    {
                        String postfix = (i == selectedVoteItem.Count - 1) ? ";" : ",";
                        sBuilder.Append("(" + selectedVoteItem[i] + ",'" + selectedVoteItem[i] + "'," + voteid + ")" + postfix);

                    }

                    if (selectedVoteItem.Count > 0)
                    {
                        command.CommandText = sBuilder.ToString();
                        command.ExecuteNonQuery();
                    }
                }

                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
            }
            finally
            {
                conn.Close();
                tran.Dispose();
                conn.Dispose();
            }


            return result;
        }



        /// <summary>
        /// 保存活动，分组，选项
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public Int32 SaveActionVoteDetail(ActionDataEntity entity)
        {
            Boolean result = true;
            MySqlConnection conn = new MySqlConnection(MySqlHelper.ConnectionStringManager);
            conn.Open();
            MySqlTransaction tran = conn.BeginTransaction();
            MySqlCommand command = new MySqlCommand();
            MySqlParameter[] parms;
            command.Connection = conn;
            command.Transaction = tran;
            command.CommandType = CommandType.Text;
            Object objActionid = null, objGroupid = null;
            Int32 actionId = 0, groupId = 0;

            //String sqlDeleteItem = @"DELETE FROM item WHERE `Groupid` in(?GroupIds);";
            //String sqlDeleteGroup = @"DELETE FROM `group` WHERE Id in(?GroupIds);";
            String sqlDeleteItem = @"DELETE FROM item WHERE `Groupid` in({0});";
            String sqlDeleteGroup = @"DELETE FROM `group` WHERE Id in({0});";
            String sqlUpdateAction = @"UPDATE `action` SET Actionname=?Actionname, Begintime=?Begintime, Endtime=?Endtime,ActionDesc=?ActionDesc WHERE Id=?Id;";

            String sqlSaveAction = @"INSERT INTO `action`(`Actionname`,Begintime,`Endtime`,`ActionDesc`)VALUES(?Actionname, ?Begintime, ?EndTime, ?ActionDesc);SELECT last_insert_id();";
            String sqlSaveGroup = @"INSERT INTO `group`(`Actionid`,`Groupname`,`MinNum`,`MaxNum`,`VoteType`,`GroupDesc`)VALUES(?Actionid, ?Groupname, ?MinNum, ?MaxNum, ?VoteType, ?GroupDesc);SELECT last_insert_id();";
            String sqlSaveItem = @"INSERT INTO `item`(`Groupid`,`Itemname`,`Itemkey`,`ImageUrl`,`Videourl`,`Desc`)VALUES";
            try
            {
                //新建活动
                if (0 == entity.ID)
                {
                    command.Parameters.Clear();
                    parms = new MySqlParameter[] { 
                        new MySqlParameter("Actionname", MySqlDbType.String),
                        new MySqlParameter("BeginTime", MySqlDbType.Datetime),
                        new MySqlParameter("EndTime", MySqlDbType.Datetime),
                        new MySqlParameter("ActionDesc", MySqlDbType.String)
                    };
                    parms[0].Value = entity.ActionName;
                    parms[1].Value = entity.BeginTime;
                    parms[2].Value = entity.EndTime;
                    parms[3].Value = entity.ActionDesc;
                    //TODO
                    command.Parameters.AddRange(parms);
                    command.CommandText = sqlSaveAction;
                    objActionid = command.ExecuteScalar();
                    result &= (null != objActionid);
                    //活动主键
                    actionId = Convert.ToInt32(objActionid);
                }
                else
                {
                    //编辑活动
                    command.Parameters.Clear();
                    parms = new MySqlParameter[] { 
                        new MySqlParameter("Actionname", MySqlDbType.String),
                         new MySqlParameter("Begintime", MySqlDbType.Datetime),
                        new MySqlParameter("Endtime", MySqlDbType.Datetime),
                        new MySqlParameter("ActionDesc", MySqlDbType.String),
                        new MySqlParameter("Id", MySqlDbType.Int32)                        
                    };
                    parms[0].Value = entity.ActionName;
                    parms[1].Value = entity.BeginTime;
                    parms[2].Value = entity.EndTime;
                    parms[3].Value = entity.ActionDesc;
                    parms[4].Value = entity.ID;
                    command.Parameters.AddRange(parms);
                    command.CommandText = sqlUpdateAction;
                    result &= (command.ExecuteNonQuery() > 0);
                    //活动主键
                    actionId = entity.ID;
                }

                //保存分组
                for (Int32 i = 0; i < entity.Group.Count; i++)
                {
                    command.Parameters.Clear();
                    parms = new MySqlParameter[] { 
                        new MySqlParameter("Actionid", MySqlDbType.Int32),
                        new MySqlParameter("Groupname", MySqlDbType.String),
                        new MySqlParameter("MinNum", MySqlDbType.Int32),
                        new MySqlParameter("MaxNum", MySqlDbType.Int32),
                        new MySqlParameter("VoteType", MySqlDbType.Int32),
                        new MySqlParameter("GroupDesc", MySqlDbType.String)
                    };
                    parms[0].Value = actionId;
                    parms[1].Value = entity.Group[i].GroupName;
                    parms[2].Value = entity.Group[i].MinNum;
                    parms[3].Value = entity.Group[i].MaxNum;
                    parms[4].Value = entity.Group[i].VoteType;
                    parms[5].Value = entity.Group[i].GroupDesc;
                    command.Parameters.AddRange(parms);
                    command.CommandText = sqlSaveGroup;
                    objGroupid = command.ExecuteScalar();
                    result &= (null != objGroupid);

                    //保存选项，避免多次写数据库直接拼sql语句
                    if (null != objGroupid)
                    {
                        groupId = Convert.ToInt32(objGroupid);
                        StringBuilder builder = new StringBuilder(sqlSaveItem);
                        for (int j = 0; j < entity.Group[i].Item.Count; j++)
                        {
                            String postfix = (j == entity.Group[i].Item.Count - 1) ? ";" : ",";
                            builder.Append(String.Format(@"({0},'{1}','{2}','{3}','{4}','{5}'){6}",
                                groupId,
                                entity.Group[i].Item[j].ItemName,
                                entity.Group[i].Item[j].ItemKey,
                                entity.Group[i].Item[j].ImageUrl,
                                entity.Group[i].Item[j].RelationVideo,
                                entity.Group[i].Item[j].Desc,
                                postfix));
                        }
                        command.Parameters.Clear();
                        command.CommandText = builder.ToString();
                        result &= (command.ExecuteNonQuery() > 0);
                    }
                }

                //编辑
                if (0 < entity.ID)
                {
                    if (!string.IsNullOrEmpty(entity.GroupIds))
                    {
                        command.Parameters.Clear();
                        //parms = new MySqlParameter[] { new MySqlParameter("GroupIds", MySqlDbType.String) };
                        //parms[0].Value = entity.GroupIds;
                        //command.Parameters.AddRange(parms);

                        //删除分组
                        command.CommandText = String.Format(sqlDeleteItem, entity.GroupIds);
                        result &= (command.ExecuteNonQuery() > 0);
                        //删除选项
                        command.CommandText = String.Format(sqlDeleteGroup, entity.GroupIds);
                        result &= (command.ExecuteNonQuery() > 0);
                    }


                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                result = false;
                tran.Rollback();
                throw ex;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
                conn.Dispose();
            }

            actionId = (result == false) ? -1 : actionId;
            return actionId;
        }

        /// <summary>
        /// 根据主键Id删除活动
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Boolean DeleteActionDetails(int id)
        {
            Boolean result = true;
            MySqlConnection conn = new MySqlConnection(MySqlHelper.ConnectionStringManager);
            conn.Open();
            MySqlTransaction tran = conn.BeginTransaction();
            MySqlCommand command = new MySqlCommand();
            command.Connection = conn;
            command.Transaction = tran;
            command.CommandType = CommandType.Text;

            string sqlDeleteResult = "DELETE FROM result WHERE itemid in (SELECT P1.ID FROM item P1 INNER JOIN `group` P2 ON P2.Id = P1.GroupId WHERE P2.actionid=?Actionid);";
            String sqlDeleteVote = @"DELETE FROM `vote` WHERE Actionid=?Actionid;";
            String sqlDeleteItem = @"DELETE FROM item WHERE Groupid IN(SELECT Id FROM `group` WHERE Actionid=?Actionid);";
            String sqlDeleteGroup = @"DELETE FROM`group` WHERE ActionId=?Actionid;";
            String sqlDeleteAction = @"DELETE FROM action WHERE Id=?Actionid;";

            try
            {
                MySqlParameter parm = new MySqlParameter("Actionid", MySqlDbType.Int32);
                parm.Value = id;
                command.Parameters.Add(parm);

                command.CommandText = sqlDeleteResult;
                command.ExecuteNonQuery();

                command.CommandText = sqlDeleteVote;
                command.ExecuteNonQuery();

                command.CommandText = sqlDeleteItem;
                command.ExecuteNonQuery();

                command.CommandText = sqlDeleteGroup;
                command.ExecuteNonQuery();

                command.CommandText = sqlDeleteAction;
                result &= (command.ExecuteNonQuery() > 0);

                tran.Commit();
            }
            catch (Exception ex)
            {
                result = false;
                tran.Rollback();
            }
            finally
            {
                conn.Close();
                tran.Dispose();
                conn.Dispose();
            }
            return result;
        }
    }
}
